package com.log.james.mapper;

import com.log.james.entity.EventsType;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;
import java.util.Map;

@Mapper
public interface WebLogAttackDao {
    /**
     * 获取攻击类型
     * @return
     */
    @Select("select a.`name`,count(wa.id) as num  from weblogevents wa,eventstype a,weblogstatistics wls " +
            "where wa.type = a.id and wa.weblogid = wls.id and wls.starttime >= #{st} and wls.endtime <= #{et}" +
            "GROUP BY a.`name` ORDER BY num desc")
    List<EventsType> webAttackType(@Param("st") Date st,@Param("et") Date et);

    /**
     * 获取最新的攻击事件
     * @return
     */
    @Select("select wls.starttime as time,a.`name` as type from eventstype a,weblogstatistics wls,weblogevents wa WHERE wa.type = a.id and wa.weblogid = wls.id ORDER BY wls.starttime DESC limit 1,8")
    List<Map<String,Object>> getNewestAttack();

    /**
     * 攻击溯源
     * @return
     */
    @Select("select wa.attackaddress ,count(wa.id) as num from weblogevents wa ,weblogstatistics wls where wa.weblogid = wls.id and wls.starttime >= #{st} and wls.endtime <= #{et} group by wa.attackaddress order by num")
    List<Map<String,Object>> srcAttack(@Param("st")Date st,@Param("et")Date et);

    @Select("select wls.starttime,wls.endtime from weblogevents wa,weblogstatistics wls where wa.weblogid = wls.id and wls.starttime >= #{st} and wls.endtime <= #{et} order by wls.starttime")
    List<Map<String,Object>> findTodayAttack(@Param("st")Date st,@Param("et")Date et);

    /**
     *通过时间段找到限制条数的攻击的详细情况
     * @param st
     * @param et
     * @param type
     * @param num
     * @return
     */
    @Select("select att.name as `name`,att.advice as advice,wt.attackaddress as address,ws.srcip,ws.starttime,ws.endtime,ws.url,ws.method,ws.reactnum,ws.times,ws.browser " +
            "from eventstype att,weblogevents wt,weblogstatistics ws " +
            "WHERE wt.type = att.id and wt.weblogid = ws.id" +
            " and ws.starttime >= #{st} and ws.endtime <= #{et} and att.name = #{type} order by ws.starttime desc limit 0,#{num}")
    List<Map<String,Object>> findPartMessageByTime(@Param("st")Date st,@Param("et")Date et,@Param("type") String type,@Param("num")int num);

    @Select("select att.name as `name`,att.advice as advice,wt.attackaddress as address,ws.srcip,ws.starttime,ws.endtime,ws.url,ws.method,ws.reactnum,ws.times,ws.browser " +
            "from eventstype att,weblogevents wt,weblogstatistics ws " +
            "WHERE wt.type = att.id and wt.weblogid = ws.id" +
            " and ws.starttime >= #{st} and ws.endtime <= #{et} and att.name = #{type} order by ws.starttime desc")
    List<Map<String,Object>> findAllMessageByTime(@Param("st")Date st,@Param("et")Date et,@Param("type") String type);

    /**
     * 根据攻击的开始时间获得攻击的详细情况
     * @param st
     * @return
     */
    @Select("select att.name as `name`,att.advice as advice,wt.attackaddress as address,ws.srcip,ws.starttime,ws.endtime,ws.url,ws.method,ws.reactnum,ws.times,ws.browser " +
            "from attacktype att,webattack wt,weblogstatistics ws " +
            "WHERE wt.attackid = att.id and wt.weblogid = ws.id" +
            " and ws.starttime = #{st}")
    List<Map<String,Object>> findAttackByStartTime(@Param("st")Date st);

    /**
     * 根据开始时间和类型找到攻击的详细信息
     */
    @Select("SELECT\n" +
            "\twle.starttime AS startTime,\n" +
            "\twle.endtime AS endTime,\n" +
            "\twle.srcip,\n" +
            "\twle.weblogid,\n" +
            "\twle.attackaddress,\n" +
            "\tet.`name`,\n" +
            "\tet.advice\n" +
            "FROM\n" +
            "\tweblogevents wle,\n" +
            "\teventstype et\n" +
            "WHERE\n" +
            "\twle.type = et.id\n" +
            "AND wle.starttime = #{st}\n" +
            "AND et. NAME = #{type}")
    Map<String,Object> findAttackDetailsByTimeAndType(@Param("st")Date st,@Param("type")String type);

    /**
     * 根据攻击事件当中的id，找到日志详情
     * @param id
     * @return
     */
    @Select("select * from weblogstatistics where id = #{id}")
    Map<String,Object> getWeblogDetails(@Param("id")int id);
}
